﻿/*
   Copyright 2011 Andrew Davis

    This file is part of Base.

    Base is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    Base is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with Base.  If not, see <http://www.gnu.org/licenses/>.
*/
namespace Base.Sql
{
	using System;
	using System.Data.SqlClient;
	using Helpers;
	using System.Reflection;
	using System.Text.RegularExpressions;
	using System.Diagnostics;

	/// <summary>
	/// BaseClass for the derived SQL classes to follow.
	/// </summary>
    [Serializable()]
    public class BaseClass : Prototypes.BaseClass
    {
		#region "Construtors"
		/// <summary>
		/// Generic empty constructor
		/// </summary>
		protected BaseClass() { }
		/// <summary>
		/// This constructor populates the properties of the object from the data in the DataReader.
		/// This constructor must always be extended its subclasses
		/// </summary>
		/// <param name="dataRdr">The data reader holding the values of the public properties</param>
		public BaseClass(SqlDataReader dataRdr)
		{
			for (int i = 0; i < dataRdr.FieldCount; i++)
			{
				FieldInfo fieldInfo = this.GetType().GetField("m" + dataRdr.GetName(i), (BindingFlags.NonPublic | BindingFlags.Instance));
				fieldInfo.SetValue(this, (dataRdr.IsDBNull(i) ? null : dataRdr[i]));
			}
		}
        #endregion

		#region "CRUD"
		/// <summary>
		/// This method is the entry point to the create, update and delete methods.  Which one fires depends on the status of the object.
		/// The properties of the class is picked up and used as parameters for the SQL call.
		/// </summary>
		/// <param name="connStr">The connection string to the data source</param>
		/// <param name="additionalParams">Futher arguments for the SQL call that are not properties of the object</param>
		/// <returns>Whether the database transaction was successful</returns>
		sealed public override bool Update(string connStr, object additionalParams = null)
		{
			if (objectStatus == ObjectStatus.Unchanged)
				return true;

			this.connStr = connStr;
			using (SqlConnection conn = new SqlConnection(connStr))
			{
				SqlTransaction trans = null;
				try
				{
					bool result = false;
					SqlCommand comm;				
					using (comm = new SqlCommand())
					{
						comm.Connection = conn; 
						switch (objectStatus)
						{
							case ObjectStatus.IsNew:
								Create(ref comm);
								break;
							case ObjectStatus.IsDirty:
								Update(ref comm);
								break;
							case ObjectStatus.IsOld:
								Delete(ref comm);
								break;
						}

						switch (comm.CommandType)
						{
							case System.Data.CommandType.StoredProcedure:
								conn.Open();
								SqlCommandBuilder.DeriveParameters(comm);
								conn.Close();
								break;
							case System.Data.CommandType.Text:
								// PARSE THE SQL WITH REGEX LOOKING FOR THE PARAMS
								string sqlQuery = comm.CommandText;
								MatchCollection matchCollection = Regex.Matches(sqlQuery, @"(?<Parameter>@\w*)");
								SqlParameter sqlParam;
								foreach(Match match in matchCollection)
								{
									sqlParam = comm.CreateParameter();
									sqlParam.ParameterName = match.Value;
									comm.Parameters.Add(sqlParam);
								}
								break;
						}


						if (additionalParams != null)
						{
							foreach (PropertyInfo propertyInfo in additionalParams.GetType().GetProperties())
								foreach (SqlParameter param in comm.Parameters)
									if (("@" + propertyInfo.Name).ToLower() == param.ParameterName.ToLower())
										param.Value = propertyInfo.GetValue(additionalParams, null);
						}
						
						PropertyInfo[] propertyInfos;
						propertyInfos = this.GetType().GetProperties();
						foreach (PropertyInfo propertyInfo in propertyInfos)
						{
							foreach (SqlParameter param in comm.Parameters)
								if (("@" + propertyInfo.Name).ToLower() == param.ParameterName.ToLower())
									param.Value = propertyInfo.GetValue(this, null);
						}
						conn.Open();
						trans = conn.BeginTransaction();
						comm.Transaction = trans;
						switch (objectStatus)
						{
							case ObjectStatus.IsNew:
								using (SqlDataReader dataRdr = comm.ExecuteReader())
								{
									if (dataRdr.Read())
									{
										mID = dataRdr.GetInt32(0);
										dataRdr.Close();
										result = true;
									}
								}								
								break;
							case ObjectStatus.IsDirty:
								result = comm.ExecuteNonQuery() > 0;
								break;
							case ObjectStatus.IsOld:
								result = comm.ExecuteNonQuery() > 0;
								break;
						}
						trans.Commit();
					}
					if (result)
						objectStatus = ObjectStatus.Unchanged;
					return result;
				}
				catch (Exception ex)
				{
					trans.Rollback();
					Debug.WriteLine("Message: " + ex.Message);
					Debug.WriteLine("Inner Exception: " + ex.InnerException);
					Log.LogError(ex, true, true, true);
					return false;
				}
				finally
				{
					conn.Close();
				}
			}
		}
		#region "Database Access"
		/// <summary>
		/// Use this to set the type and text the command for the create
		/// </summary>
		/// <param name="comm">The command that the transcation happens through</param>
		protected virtual void Create(ref SqlCommand comm)
		{
			throw new NotImplementedException(this.GetType().FullName + " 'Create' has not been implemented.");
		}
		/// <summary>
		/// Reads data from the database and constructs an object based on that information.
		/// The data coming from the query must be name the same the property of the class
		/// </summary>
		/// <param name="connStr">The connection string to the data source</param>
		/// <param name="sqlText">The SQL to run</param>
		/// <param name="type">The object type to return</param>
		/// <param name="commandType">The type of the command to run</param>
		/// <param name="additionalParams">A loosly typed object containing the parameters needed the the SQL</param>
		/// <returns>An object derived from the BaseClass</returns>
		protected static BaseClass Read(string connStr, string sqlText, Type type, System.Data.CommandType commandType = System.Data.CommandType.Text, object additionalParams = null)
		{
			using (SqlConnection conn = new SqlConnection(connStr))
			{
				try
				{
					using (SqlCommand comm = new SqlCommand(sqlText, conn))
					{
						comm.CommandType = commandType;
						switch (comm.CommandType)
						{
							case System.Data.CommandType.StoredProcedure:
								conn.Open();
								SqlCommandBuilder.DeriveParameters(comm);
								conn.Close();
								break;
							case System.Data.CommandType.Text:
								// PARSE THE SQL WITH REGEX LOOKING FOR THE PARAMS
								string sqlQuery = comm.CommandText;
								MatchCollection matchCollection = Regex.Matches(sqlQuery, @"(?<Parameter>@\w*)");
								SqlParameter sqlParam;
								foreach (Match match in matchCollection)
								{
									sqlParam = comm.CreateParameter();
									sqlParam.ParameterName = match.Value;
									comm.Parameters.Add(sqlParam);
								}
								break;
						}
						if (additionalParams != null)
						{
							foreach (PropertyInfo propertyInfo in additionalParams.GetType().GetProperties())
								foreach (SqlParameter param in comm.Parameters)
									if (("@" + propertyInfo.Name).ToLower() == param.ParameterName.ToLower())
										param.Value = propertyInfo.GetValue(additionalParams, null);
						}
						conn.Open();
						SqlDataReader dataRdr = comm.ExecuteReader();
						if (dataRdr.Read())
						{
							ConstructorInfo cI = type.GetConstructor(BindingFlags.NonPublic | BindingFlags.Instance, null, new Type[] { typeof(SqlDataReader) }, null);
							return cI.Invoke(new object[] { dataRdr }) as BaseClass;
						}
						return null;
					}
				}
				catch (Exception ex)
				{
					Debug.WriteLine("Message: " + ex.Message);
					Debug.WriteLine("Inner Exception: " + ex.InnerException);
					Log.LogError(ex, true, true, true);
					return null;
				}
				finally
				{
					conn.Close();
				}
			}
		}
		/// <summary>
		/// Use this to set the type and text the command for the update
		/// </summary>
		/// <param name="comm">The command that the transcation happens through</param>
		protected virtual void Update(ref SqlCommand comm)
		{
			throw new NotImplementedException(this.GetType().FullName + " 'Update' has not been implemented.");
		}
		/// <summary>
		/// Use this to set the type and text the command for the delete
		/// </summary>
		/// <param name="comm">The command that the transcation happens through</param>
		protected virtual void Delete(ref SqlCommand comm)
		{
			throw new NotImplementedException(this.GetType().FullName + " 'Delete' has not been implemented.");
		}
		#endregion
		#endregion        
    }
}
